import csv
from datetime import datetime
from passlib.context import CryptContext

from sqlalchemy import create_engine, exc, select
from sqlalchemy.orm import sessionmaker

from models.models import (
    FileStatistic,
    Modality,
    Statistic,
    # DoctorsInfo,
    User,
    Role,
    UserModality,
)


engine = create_engine("postgresql://user:pass@postgres_db:5432/dbname")
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")


def load_statistics_data(file_path: str, id_filename: int):
    """Загружает содержимое файла в базу данных."""
    session = SessionLocal()
    with open(file_path, "r", encoding="utf-8") as csvfile:
        reader = csv.reader(csvfile)
        next(reader, None)
        for row in reader:
            record = Statistic(
                id_filename=id_filename,
                year=int(row[0]),
                week=int(row[1]),
                date=row[2],
                wc=int(row[3]),
                kind=row[4],
                modality=row[5],
                type=row[6],
                number=int(row[7]),
            )
            session.add(record)
    try:
        session.commit()
        print("Загружены данные о врачах в базу данных.")
    except exc.IntegrityError:
        session.rollback()
        print("Произошла ошибка при добавлении данных в базу данных.")
    finally:
        session.close()


def load_doctors_info_from_txt(file_path: str):
    """Загружает данные из txt файла и создает пользователей-врачей."""
    session = SessionLocal()

    query = select(Role).where(Role.name == "doctor")
    query_result = session.execute(query)
    doctor_role = query_result.scalar_one()

    with open(file_path, "r") as file:
        next(file)
        for line in file:
            name, rate, modality, additional_modality = line.split(";")

            query = select(Modality).where(Modality.name == modality)
            query_result = session.execute(query)
            modality = query_result.scalar_one()

            new_doctor = User(
                username=name,
                login=name,
                hashed_password=pwd_context.hash(name),
                modality_id=modality.id,
                employment_rate=rate,
                start_work=datetime.now().date(),
                status="Работает",
            )
            new_doctor.role_id = doctor_role.id
            session.add(new_doctor)
        try:
            session.commit()
        except exc.IntegrityError:
            session.rollback()
            print("Пользователи уже существуют.")
        finally:
            session.close()
            print("Созданы пользователи-врачи.")


def write_filename(filename: str):
    """Записывает имя файла в БД."""
    session = SessionLocal()
    new_file = FileStatistic(
        filename=filename,
    )
    session.add(new_file)
    try:
        session.commit()
        session.refresh(new_file)
    except exc.IntegrityError:
        session.rollback()
        print("Файл уже существует в базе данных.")
        return
    finally:
        session.close()
    print("Записано имя файла в базу данных.")

    load_statistics_data("download/statistics.csv", id_filename=new_file.id)


def create_modality():
    """Создаем модальности."""
    session = SessionLocal()

    list_modality: list = [
        "РГ",
        "КТ",
        "МРТ",
        "ММГ",
        "Флг",
        "Денс",
        "РХ",
        "НДКТ",
        "ПЭТ/КТ",
    ]
    for modality in list_modality:
        record = Modality(name=modality)
        session.add(record)
    try:
        session.commit()
        print("Созданы модальности.")
    except exc.IntegrityError:
        session.rollback()
        print("Модальности уже существуют.")
    finally:
        session.close()


def create_roles():
    """Создаем роли."""
    session = SessionLocal()

    admin_role = Role(
        name="admin",
        fullname="Администратор",
    )
    session.add(admin_role)

    boss_role = Role(
        name="boss",
        fullname="Руководитель",
    )
    session.add(boss_role)

    hr_role = Role(name="hr", fullname="Кадровик")
    session.add(hr_role)

    doctor_role = Role(name="doctor", fullname="Врач")
    session.add(doctor_role)

    try:
        session.commit()
        print("Созданы роли.")
    except exc.IntegrityError:
        session.rollback()
        print("Роли уже существуют.")


def create_users():
    """Создаем пользователей."""
    session = SessionLocal()

    query = select(Role).where(Role.name == "boss")
    query_result = session.execute(query)
    boss_role = query_result.scalar_one()
    new_boss = User(
        username="Руководитель",
        login="boss",
        hashed_password=pwd_context.hash("boss"),
        role_id=boss_role.id,
        employment_rate="1",
        start_work=datetime.now().date(),
        status="Работает",
    )
    session.add(new_boss)

    query = select(Role).where(Role.name == "hr")
    query_result = session.execute(query)
    hr_role = query_result.scalar_one()
    new_hr = User(
        username="Кадровик",
        login="hr",
        hashed_password=pwd_context.hash("hr"),
        role_id=hr_role.id,
        employment_rate="1",
        start_work=datetime.now().date(),
        status="Работает",
    )
    session.add(new_hr)

    query = select(Role).where(Role.name == "doctor")
    query_result = session.execute(query)
    doctor_role = query_result.scalar_one()
    new_doctor = User(
        username="Врач",
        login="doctor",
        hashed_password=pwd_context.hash("doctor"),
        role_id=doctor_role.id,
        employment_rate="1",
        start_work=datetime.now().date(),
        status="Работает",
    )
    session.add(new_doctor)

    try:
        session.commit()
        print("Созданы пользователи.")
    except exc.IntegrityError:
        session.rollback()
        print("Пользователи уже существуют.")
    finally:
        session.close()


def load_super_user():
    """Загружает данные супер пользователя."""
    session = SessionLocal()
    query = select(Role).where(Role.name == "admin")
    query_result = session.execute(query)
    admin_role = query_result.scalar_one()

    super_user = User(
        username="Администратор",
        login="admin",
        hashed_password=pwd_context.hash("admin"),
    )
    super_user.role_id = admin_role.id
    session.add(super_user)
    try:
        session.commit()
        print("Загружен супер пользователь.")
    except exc.IntegrityError:
        session.rollback()
        print("Супер пользователь уже существует.")
    finally:
        session.close()


def add_additional_modality_to_doctors(file_path: str):
    """Добавляет дополнительные модальности врачей."""
    data = []
    with open(file_path, "r") as file:
        next(file)
        for line in file:
            name, rate, modality, additional_modalities = line.strip().split(";")
            data.append((name, rate, modality, additional_modalities))

    session = SessionLocal()
    for item in data:
        name, rate, modality, additional_modalities = item
        try:
            user = session.query(User).filter_by(username=name).one_or_none()
            if user:
                additional_modalities_list = additional_modalities.split(",")
                for modality_name in additional_modalities_list:
                    modality_obj = (
                        session.query(Modality)
                        .filter_by(name=modality_name.strip())
                        .first()
                    )
                    if modality_obj:
                        user_modality_record = UserModality(
                            user_id=user.id, modality_id=modality_obj.id
                        )
                        session.add(user_modality_record)
        except Exception as e:
            print(f"Ошибка при обработке данных: {e}")
    session.commit()
    session.close()


if __name__ == "__main__":
    write_filename("statistics.csv")
    create_modality()
    create_roles()
    create_users()
    load_super_user()
    load_doctors_info_from_txt(file_path="download/doctors_list.txt")
    add_additional_modality_to_doctors(file_path="download/doctors_list.txt")
